/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import model.entities.Teacher;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.dao.service.TeacherDAOService;
import model.entities.Course;

/**
 *
 * @author K u Ku
 */
public class TeacherDAO implements TeacherDAOService {
    private static TeacherDAO teacherDAO;
    
    public static TeacherDAO getInstance(){
        if(teacherDAO == null){
            teacherDAO = new TeacherDAO();
        }
        return teacherDAO;
    }
    
    //Function get all teachers
    @Override
    public List<Teacher> getTeachersList(){
        ArrayList<Teacher> lstTeacher = new ArrayList<Teacher>();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblTeacher WHERE IsActived='true'";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                Teacher t = new Teacher();
                t.setTeacherID(rs.getInt("TeacherID"));
                t.setName(rs.getString("Name"));
                t.setIdentityCard(rs.getString("IdentityCard"));
                t.setGender(rs.getBoolean("Gender"));
                t.setBirthday(rs.getDate("Birthday"));
                t.setPhone(rs.getString("Phone"));
                t.setEmail(rs.getString("Email"));
                t.setAddress(rs.getString("Address"));
                t.setNotes(rs.getString("Notes"));
                List<model.entities.Course> courseList = CourseDAO.getInstance().getCourseByTeacherID(rs.getInt("TeacherID"));
                t.setListCourse(courseList);
                lstTeacher.add(t);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TeacherDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstTeacher;
    }
    
    //Function get teacher by ID
    @Override
    public Teacher getTeacherByID(int TeacherID){
        Teacher tea = new Teacher();
        try {
            Connection conn = ConnectionFactory.getConnection();
            String sql = "select * from tblTeacher where TeacherID = ? and isActived ='true'";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, TeacherID);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("TeacherID");
                tea.setTeacherID(id);
                tea.setName(rs.getString("Name"));
                tea.setBirthday(rs.getDate("BirthDay"));
                tea.setGender(rs.getBoolean("Gender"));
                tea.setAddress(rs.getString("Address"));
                tea.setIdentityCard(rs.getString("IdentityCard"));
                tea.setPhone(rs.getString("Phone"));
                tea.setEmail(rs.getString("Email"));
                List<Course> courseList = CourseDAO.getInstance().getCourseByTeacherID(id);
                tea.setListCourse(courseList);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return tea;
    }

    @Override
    public boolean insertTeacher(Teacher t) {
        String query = "INSERT INTO tblTeacher VALUES(?,?,?,?,?,?,?,?,?)";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setNString(1, t.getName());
            pstmt.setString(2, t.getIdentityCard());
            pstmt.setBoolean(3, t.isGender());
            pstmt.setDate(4, (Date) t.getBirthday());
            pstmt.setString(5, t.getPhone());
            pstmt.setString(6, t.getEmail());
            pstmt.setNString(7, t.getAddress());
            pstmt.setNString(8, t.getNotes());
            pstmt.setBoolean(9, t.isIsActived());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(TeacherDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean updateTeacher(Teacher t) {
        String query = "UPDATE tblTeacher SET Name=?, IdentityCard=?, Gender=?, "
                + "Birthday=?, Phone=?, Email=?, [Address]=?, Notes=?, IsActived=?" +
                        " WHERE TeacherID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setNString(1, t.getName());
            pstmt.setString(2, t.getIdentityCard());
            pstmt.setBoolean(3, t.isGender());
            pstmt.setDate(4, (Date) t.getBirthday());
            pstmt.setString(5, t.getPhone());
            pstmt.setString(6, t.getEmail());
            pstmt.setNString(7, t.getAddress());
            pstmt.setNString(8, t.getNotes());
            pstmt.setBoolean(9, t.isIsActived());
            pstmt.setInt(10, t.getTeacherID());
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(TeacherDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public boolean deleteTeacher(int id) {
        String query = "UPDATE tblTeacher SET IsActived='false' WHERE TeacherID=?";
        Connection conn = ConnectionFactory.getConnection();
        try {
            PreparedStatement pstmt = conn.prepareCall(query);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(TeacherDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    @Override
    public Teacher findByID(int id) {
        Teacher t = new Teacher();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblTeacher WHERE TeacherID= ? and IsActived='true'";
        try {
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                t.setTeacherID(rs.getInt("TeacherID"));
                t.setName(rs.getString("Name"));
                t.setIdentityCard(rs.getString("IdentityCard"));
                t.setGender(rs.getBoolean("Gender"));
                t.setAddress(rs.getString("Address"));
                t.setBirthday(rs.getDate("Birthday"));
                t.setEmail(rs.getString("Email"));
                t.setNotes(rs.getString("Notes"));
                t.setPhone(rs.getString("Phone"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(TeacherDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return t;
    }

    @Override
    public List<Teacher> findByName(String name) {
        List<Teacher> lstTeacher = new ArrayList<Teacher>();
        Teacher t = new Teacher();
        Connection conn = ConnectionFactory.getConnection();
        String query = "SELECT * FROM tblTeacher WHERE Name= ? and IsActived='true'";
        try {
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.setNString(1, name);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                t.setTeacherID(rs.getInt("TeacherID"));
                t.setName(rs.getNString("Name"));
                t.setIdentityCard(rs.getString("IdentityCard"));
                t.setGender(rs.getBoolean("Gender"));
                t.setAddress(rs.getNString("Address"));
                t.setBirthday(rs.getDate("Birthday"));
                t.setEmail(rs.getString("Email"));
                t.setNotes(rs.getString("Notes"));
                t.setPhone(rs.getString("Phone"));
                lstTeacher.add(t);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TeacherDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstTeacher;
    }
}
